package com.mallcai.bigdata.ladon.dpl.mapper.report.provider;

import com.google.common.base.Joiner;
import com.mallcai.bigdata.ladon.dpl.entity.report.Report;
import com.mallcai.bigdata.ladon.dpl.mapper.BaseSqlProvider;
import com.mallcai.bigdata.ladon.dpl.mapper.system.provider.RoleRlReportSqlProvider;
import com.mallcai.bigdata.ladon.dpl.mapper.system.provider.UserSqlProvider;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.jdbc.SQL;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @author gst
 * @date 2019-12-30 20:36
 */
public class ReportSqlProvider extends BaseSqlProvider<Report> {

    public static final String TB_REPORT = "r_report";

    /**
     * 获取表名
     *
     * @return String
     */
    @Override
    public String getTableName() {
        return TB_REPORT;
    }

    /**
     * 获取插入数据的字段列表
     *
     * @return Map
     */
    @Override
    public Map<String, String> getInsertColumns() {
        Map<String, String> map = new LinkedHashMap<>();
        map.put("code", "#{code}");
        map.put("name", "#{name}");
        map.put("type", "#{type}");
        map.put("x_axis", "#{xAxis}");
        map.put("y_axis", "#{yAxis}");
        map.put("icon", "#{icon}");
        map.put("detail_sql_id", "#{detailSqlId}");
        map.put("count_sql_id", "#{countSqlId}");
        map.put("query_field_label_len", "#{queryFieldLabelLen}");
        map.put("query_field_media_len", "#{queryFieldMediaLen}");
        map.put("simple_report", "#{simpleReport}");
        map.put("statical", "#{statical}");
        map.put("flow_switch", "#{flowSwitch}");
        map.put("sort_id", "#{sortId}");
        map.put("comment", "#{comment}");
        map.put("owner", "#{owner}");
        return map;
    }

    /**
     * 获取更新操作的字段列表
     *
     * @return Map
     */
    @Override
    public Map<String, String> getUpdateColumns() {
        Map<String, String> map = new LinkedHashMap<>();
        map.put("code", "code");
        map.put("name", "name");
        map.put("type", "type");
        map.put("xAxis", "x_axis");
        map.put("yAxis", "y_axis");
        map.put("icon", "icon");
        map.put("detailSqlId", "detail_sql_id");
        map.put("countSqlId", "count_sql_id");
        map.put("queryFieldLabelLen", "query_field_label_len");
        map.put("queryFieldMediaLen", "query_field_media_len");
        map.put("simpleReport", "simple_report");
        map.put("statical", "statical");
        map.put("flowSwitch", "flow_switch");
        map.put("sortId", "sort_id");
        map.put("comment", "comment");
        map.put("owner", "owner");
        return map;
    }

    /**
     * 根据参数查询
     */
    public String getByParams(Map<String, Object> map) {
        Map<String, Object> params = getParams(map);

        StringBuilder sb = new StringBuilder();
        sb.append("tb_r.archive = 0");

        if (params.containsKey("name") && StringUtils.isNotBlank(params.get("name").toString())) {
            sb.append(" and tb_r.`name`  like CONCAT('%', #{params.name}, '%')");
        }
        if (params.containsKey("code") && StringUtils.isNotBlank(params.get("code").toString())) {
            sb.append(" and tb_r.`code`  like CONCAT('%', #{params.code}, '%')");
        }
        if (params.containsKey("groupId")) {
            sb.append(" and tb_rl_g.group_id = #{params.groupId}");
        }
        if (params.containsKey("owner")) {
            sb.append(" and tb_r.owner = #{params.owner}");
        }

        StringBuilder fields = new StringBuilder(" DISTINCT tb_r.id AS d_id, tb_r.*, tb_u.nickname as owner_name, ");
        fields.append("(")
                .append(" SELECT group_concat(tb_tmp.`name`) FROM (")
                .append("       SELECT")
                .append("        	tb_g.`name` ,")
                .append("        	tb_g.id ,")
                .append("        	tb_rl.group_id ,")
                .append("        	tb_rl.report_id ,")
                .append("        	tb_g.archive")
                .append("       FROM r_report_rl_group tb_rl")
                .append("        LEFT JOIN r_group tb_g ON tb_g.id = tb_rl.group_id")
                .append("    ) tb_tmp ")
                .append("  WHERE tb_tmp.archive = 0 AND tb_tmp.report_id = tb_r.id")
                .append(" ) groupNameStr ,");
        fields.append("(")
                .append(" SELECT group_concat(tb_tmp.id) FROM (")
                .append("       SELECT")
                .append("        	tb_g.`name` ,")
                .append("        	tb_g.id ,")
                .append("        	tb_rl.group_id ,")
                .append("        	tb_rl.report_id ,")
                .append("        	tb_g.archive")
                .append("       FROM r_report_rl_group tb_rl")
                .append("        LEFT JOIN r_group tb_g ON tb_g.id = tb_rl.group_id")
                .append("    ) tb_tmp ")
                .append("  WHERE tb_tmp.archive = 0 AND tb_tmp.report_id = tb_r.id")
                .append(" ) groupIdStr ");

        return new SQL() {
            {
                SELECT(fields.toString());
                FROM(getTableName() + " tb_r");
                LEFT_OUTER_JOIN(UserSqlProvider.TB_USER + " tb_u ON tb_u.id = tb_r.owner");
                LEFT_OUTER_JOIN(ReportRlGroupSqlProvider.TB_REPORT_RL_GROUP + " tb_rl_g ON tb_rl_g.report_id = tb_r.id");
                WHERE(sb.toString());
                ORDER_BY(" tb_r.sort_id DESC");
            }
        }.toString();
    }

    /**
     * 判断是否有相同的名称
     */
    public String hasSameName(@Param("groupIds") List<Long> groupIds, @Param("name") String name, @Param("filterId") Long filterId) {
        StringBuilder sb = new StringBuilder();
        sb.append("archive = 0")
                .append(" AND name = #{name}")
                .append(" AND id in ( SELECT report_id FROM ")
                .append(ReportRlGroupSqlProvider.TB_REPORT_RL_GROUP)
                .append(" WHERE archive = 0 AND group_id in (").append(Joiner.on(",").join(groupIds)).append(")")
                .append(")");
        if (filterId != null) {
            sb.append(" AND id != #{filterId}");
        }

        return new SQL() {
            {
                SELECT("count(0)");
                FROM(getTableName());
                WHERE(sb.toString());
            }
        }.toString();
    }

    /**
     * 判断是否有相同的编码
     */
    public String hasSameCode(@Param("groupIds") List<Long> groupIds, @Param("code") String code, @Param("filterId") Long filterId) {
        StringBuilder sb = new StringBuilder();
        sb.append("archive = 0")
                .append(" AND code = #{code}")
                .append(" AND id in ( SELECT report_id FROM ")
                .append(ReportRlGroupSqlProvider.TB_REPORT_RL_GROUP)
                .append(" WHERE archive = 0 AND group_id in (").append(Joiner.on(",").join(groupIds)).append(")")
                .append(")");
        if (filterId != null) {
            sb.append(" AND id != #{filterId}");
        }

        return new SQL() {
            {
                SELECT("count(0)");
                FROM(getTableName());
                WHERE(sb.toString());
            }
        }.toString();
    }

    public String getSimpleReport(@Param("simpleReport") Integer simpleReport) {
        StringBuilder whereStr = new StringBuilder("tb_r.archive = 0");
        if (simpleReport != null) {
            whereStr.append(" AND tb_r.simple_report = #{simpleReport}");
        }

        StringBuilder fields = new StringBuilder(" DISTINCT tb_r.id AS id, tb_r.`code`, tb_r.`name`,  ");
        fields.append("(")
                .append(" SELECT group_concat(tb_tmp.id) FROM (")
                .append("       SELECT")
                .append("        	tb_g.`name` ,")
                .append("        	tb_g.id ,")
                .append("        	tb_rl.group_id ,")
                .append("        	tb_rl.report_id ,")
                .append("        	tb_g.archive")
                .append("       FROM r_report_rl_group tb_rl")
                .append("        LEFT JOIN r_group tb_g ON tb_g.id = tb_rl.group_id")
                .append("    ) tb_tmp ")
                .append("  WHERE tb_tmp.archive = 0 AND tb_tmp.report_id = tb_r.id")
                .append(" ) groupIdStr ");

        return new SQL() {
            {
                SELECT(fields.toString());
                FROM(getTableName() + " tb_r");
                WHERE(whereStr.toString());
            }
        }.toString();
    }

    public String getOwnSimpleReport(@Param("createdBy") Long createdBy, @Param("roleIds") List<Long> roleIds) {
        StringBuilder whereStr = new StringBuilder("tb_r.archive = 0");
        whereStr.append(" AND ( tb_r.created_by = #{createdBy}");
        if (roleIds != null && roleIds.size() > 0) {
            whereStr.append(" OR  id IN (")
                    .append("      SELECT report_id FROM ").append(RoleRlReportSqlProvider.TB_ROLE_RL_REPORT)
                    .append("               WHERE  role_id IN (").append(Joiner.on(",").join(roleIds)).append(")")
                    .append("             )");
        }
        whereStr.append(")");


        StringBuilder fields = new StringBuilder(" DISTINCT tb_r.id AS id, tb_r.`code`, tb_r.`name`,  ");
        fields.append("(")
                .append(" SELECT group_concat(tb_tmp.id) FROM (")
                .append("       SELECT")
                .append("        	tb_g.`name` ,")
                .append("        	tb_g.id ,")
                .append("        	tb_rl.group_id ,")
                .append("        	tb_rl.report_id ,")
                .append("        	tb_g.archive")
                .append("       FROM r_report_rl_group tb_rl")
                .append("        LEFT JOIN r_group tb_g ON tb_g.id = tb_rl.group_id")
                .append("    ) tb_tmp ")
                .append("  WHERE tb_tmp.archive = 0 AND tb_tmp.report_id = tb_r.id")
                .append(" ) groupIdStr ");

        return new SQL() {
            {
                SELECT(fields.toString());
                FROM(getTableName() + " tb_r");
                WHERE(whereStr.toString());
            }
        }.toString();
    }


    /**
     * 根据code查询
     *
     * @param code String
     * @return String
     */
    public String findByCode(@Param("code") String code) {
        return new SQL() {
            {
                SELECT("*");
                FROM(getTableName());
                WHERE("archive = 0 AND code = #{code}");
            }
        }.toString();
    }

    public String getReportCodeAndName() {
        return new SQL() {
            {
                SELECT("code,name");
                FROM(getTableName());
                WHERE("archive = 0");
            }
        }.toString();
    }

    public String getByIds(@Param("ids") List<Long> ids) {
        StringBuffer sb = new StringBuffer();
        if (ids == null || ids.size() <= 0) {
            sb.append(" 1 = 0 ");
        } else {
            sb.append(" archive = 0 ")
                    .append(" AND id IN  (")
                    .append(Joiner.on(",").join(ids))
                    .append(")");
        }

        return new SQL() {
            {
                SELECT("id, name");
                FROM(getTableName());
                WHERE(sb.toString());
            }
        }.toString();
    }
}
